#!/bin/bash

# MySQL访问权限修复脚本
# 解决root和maxwell用户连接问题

echo "=== MySQL访问权限修复脚本 ==="
echo "时间: $(date)"
echo ""

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 配置变量
MYSQL_HOST="192.168.10.102"
MYSQL_PORT="3306"
ROOT_PASSWORD="your_root_password"  # 请替换为实际密码
NEW_ROOT_PASSWORD="new_root_password"  # 新的root密码
MAXWELL_PASSWORD="maxwell"

echo -e "${BLUE}1. 检查当前MySQL状态${NC}"
echo "----------------------------------------"

# 检查MySQL服务状态
if systemctl is-active --quiet mysqld; then
    echo -e "${GREEN}✓ MySQL服务正在运行${NC}"
else
    echo -e "${RED}✗ MySQL服务未运行，正在启动...${NC}"
    systemctl start mysqld
    sleep 3
    if systemctl is-active --quiet mysqld; then
        echo -e "${GREEN}✓ MySQL服务已启动${NC}"
    else
        echo -e "${RED}✗ MySQL服务启动失败${NC}"
        exit 1
    fi
fi

echo ""
echo -e "${BLUE}2. 尝试本地连接MySQL${NC}"
echo "----------------------------------------"

# 尝试多种本地连接方式
echo "尝试本地连接MySQL..."

# 方式1：使用socket连接
if mysql -u root -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 本地socket连接成功${NC}"
    LOCAL_CONNECTION="socket"
elif mysql -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 本地密码连接成功${NC}"
    LOCAL_CONNECTION="password"
else
    echo -e "${YELLOW}⚠ 本地连接失败，尝试重置密码${NC}"
    LOCAL_CONNECTION="failed"
fi

echo ""
echo -e "${BLUE}3. 重置MySQL root密码（如果需要）${NC}"
echo "----------------------------------------"

if [ "$LOCAL_CONNECTION" = "failed" ]; then
    echo "重置MySQL root密码..."
    
    # 停止MySQL服务
    systemctl stop mysqld
    
    # 启动MySQL安全模式
    echo "启动MySQL安全模式..."
    mysqld_safe --skip-grant-tables --skip-networking &
    sleep 5
    
    # 重置密码
    mysql -u root << EOF
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '$NEW_ROOT_PASSWORD';
FLUSH PRIVILEGES;
EOF
    
    # 停止安全模式MySQL
    pkill mysqld
    sleep 3
    
    # 重启MySQL服务
    systemctl start mysqld
    sleep 5
    
    # 测试新密码
    if mysql -u root -p$NEW_ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
        echo -e "${GREEN}✓ 密码重置成功${NC}"
        ROOT_PASSWORD=$NEW_ROOT_PASSWORD
    else
        echo -e "${RED}✗ 密码重置失败${NC}"
        exit 1
    fi
fi

echo ""
echo -e "${BLUE}4. 创建和配置maxwell用户${NC}"
echo "----------------------------------------"

# 根据连接方式选择命令
if [ "$LOCAL_CONNECTION" = "socket" ]; then
    MYSQL_CMD="mysql -u root"
elif [ "$LOCAL_CONNECTION" = "password" ] || [ "$LOCAL_CONNECTION" = "failed" ]; then
    MYSQL_CMD="mysql -u root -p$ROOT_PASSWORD"
else
    echo -e "${RED}✗ 无法确定MySQL连接方式${NC}"
    exit 1
fi

echo "创建maxwell用户..."
$MYSQL_CMD << EOF
-- 删除已存在的maxwell用户
DROP USER IF EXISTS 'maxwell'@'%';
DROP USER IF EXISTS 'maxwell'@'localhost';

-- 创建maxwell用户
CREATE USER 'maxwell'@'%' IDENTIFIED BY '$MAXWELL_PASSWORD';

-- 授予必要权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxwell'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON maxwell.* TO 'maxwell'@'%';

-- 创建maxwell数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS maxwell;

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证用户创建
SELECT user, host FROM mysql.user WHERE user='maxwell';
EOF

echo ""
echo -e "${BLUE}5. 配置root用户远程访问${NC}"
echo "----------------------------------------"

echo "配置root用户远程访问..."
$MYSQL_CMD << EOF
-- 创建root@%用户（如果不存在）
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '$ROOT_PASSWORD';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证用户
SELECT user, host FROM mysql.user WHERE user='root';
EOF

echo ""
echo -e "${BLUE}6. 配置MySQL允许远程连接${NC}"
echo "----------------------------------------"

# 检查并修改MySQL配置
echo "检查MySQL配置文件..."
MYSQL_CONF=""
if [ -f /etc/my.cnf ]; then
    MYSQL_CONF="/etc/my.cnf"
elif [ -f /etc/mysql/my.cnf ]; then
    MYSQL_CONF="/etc/mysql/my.cnf"
fi

if [ -n "$MYSQL_CONF" ]; then
    echo "配置文件: $MYSQL_CONF"
    
    # 备份配置文件
    cp $MYSQL_CONF ${MYSQL_CONF}.backup.$(date +%Y%m%d_%H%M%S)
    
    # 检查bind-address配置
    if grep -q "bind-address" $MYSQL_CONF; then
        echo "修改bind-address配置..."
        sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' $MYSQL_CONF
    else
        echo "添加bind-address配置..."
        echo "" >> $MYSQL_CONF
        echo "[mysqld]" >> $MYSQL_CONF
        echo "bind-address = 0.0.0.0" >> $MYSQL_CONF
    fi
    
    # 检查skip-networking配置
    if grep -q "skip-networking" $MYSQL_CONF; then
        echo "注释掉skip-networking配置..."
        sed -i 's/^skip-networking/#skip-networking/' $MYSQL_CONF
    fi
    
    echo -e "${GREEN}✓ MySQL配置已更新${NC}"
else
    echo -e "${YELLOW}⚠ 未找到MySQL配置文件${NC}"
fi

echo ""
echo -e "${BLUE}7. 配置防火墙${NC}"
echo "----------------------------------------"

# 配置防火墙
echo "配置防火墙允许MySQL端口..."
if command -v firewall-cmd >/dev/null 2>&1; then
    firewall-cmd --permanent --add-port=3306/tcp
    firewall-cmd --reload
    echo -e "${GREEN}✓ 防火墙配置完成${NC}"
else
    echo -e "${YELLOW}⚠ 未找到firewall-cmd，请手动配置防火墙${NC}"
fi

echo ""
echo -e "${BLUE}8. 重启MySQL服务${NC}"
echo "----------------------------------------"

# 重启MySQL服务
echo "重启MySQL服务..."
systemctl restart mysqld
sleep 5

if systemctl is-active --quiet mysqld; then
    echo -e "${GREEN}✓ MySQL服务重启成功${NC}"
else
    echo -e "${RED}✗ MySQL服务重启失败${NC}"
    exit 1
fi

echo ""
echo -e "${BLUE}9. 测试连接${NC}"
echo "----------------------------------------"

# 测试本地连接
echo "测试本地连接..."
if mysql -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 本地root连接成功${NC}"
else
    echo -e "${RED}✗ 本地root连接失败${NC}"
fi

# 测试本地maxwell连接
echo "测试本地maxwell连接..."
if mysql -u maxwell -p$MAXWELL_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 本地maxwell连接成功${NC}"
else
    echo -e "${RED}✗ 本地maxwell连接失败${NC}"
fi

# 测试远程连接
echo "测试远程连接..."
if mysql -h $MYSQL_HOST -P $MYSQL_PORT -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 远程root连接成功${NC}"
else
    echo -e "${RED}✗ 远程root连接失败${NC}"
fi

# 测试远程maxwell连接
echo "测试远程maxwell连接..."
if mysql -h $MYSQL_HOST -P $MYSQL_PORT -u maxwell -p$MAXWELL_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 远程maxwell连接成功${NC}"
else
    echo -e "${RED}✗ 远程maxwell连接失败${NC}"
fi

echo ""
echo -e "${BLUE}10. 连接信息总结${NC}"
echo "----------------------------------------"

echo "MySQL连接信息："
echo "  主机: $MYSQL_HOST"
echo "  端口: $MYSQL_PORT"
echo "  Root密码: $ROOT_PASSWORD"
echo "  Maxwell密码: $MAXWELL_PASSWORD"
echo ""
echo "连接命令："
echo "  本地root: mysql -u root -p$ROOT_PASSWORD"
echo "  本地maxwell: mysql -u maxwell -p$MAXWELL_PASSWORD"
echo "  远程root: mysql -h $MYSQL_HOST -P $MYSQL_PORT -u root -p$ROOT_PASSWORD"
echo "  远程maxwell: mysql -h $MYSQL_HOST -P $MYSQL_PORT -u maxwell -p$MAXWELL_PASSWORD"

echo ""
echo "=== 修复完成 ===" 